package com.googlecode.flyway.core.dbsupport.sybase;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.googlecode.flyway.core.dbsupport.DbSupport;
import com.googlecode.flyway.core.migration.sql.PlaceholderReplacer;
import com.googlecode.flyway.core.migration.sql.SqlScript;
import com.googlecode.flyway.core.migration.sql.SqlStatement;

public class SybaseDbSupport extends DbSupport {

    public SybaseDbSupport(Connection connection) {
        super(new SybaseJdbcTemplate(connection));
    }

    public String getScriptLocation() {
        return "com/googlecode/flyway/core/dbsupport/sybase/";
    }

    public String getCurrentUserFunction() {
        return "suser_name()";
    }

    public String getCurrentSchema() throws SQLException {
        return jdbcTemplate.queryForString("select user_name()");
    }

    public boolean isSchemaEmpty(String schema) throws SQLException {
        int objectCount = jdbcTemplate.queryForInt("select count(1) from sysobjects where type in ('U')");
        objectCount += jdbcTemplate.queryForInt("select count(1) from sysobjects where type='V' and name not like 'sys%'");
        return objectCount == 0;
    }

    public boolean tableExists(final String schema, final String table) throws SQLException {
        return jdbcTemplate.hasTables(null, schema.toLowerCase(), table.toLowerCase());
    }

    public boolean supportsDdlTransactions() {
        return false;
    }

    public void lockTable(String schema, String table) throws SQLException {
        jdbcTemplate.update("lock table " + schema + "." + table + " in exclusive mode");
    }

    public String getBooleanTrue() {
        return "1";
    }

    public String getBooleanFalse() {
        return "0";
    }

    public SqlScript createSqlScript(String sqlScriptSource, PlaceholderReplacer placeholderReplacer) {
        return new SybaseSqlScript(sqlScriptSource, placeholderReplacer);
    }

    public SqlScript createCleanScript(String schema) throws SQLException {
        List<String> statements = cleanForeignKeys(schema);
        statements.addAll(cleanViews(schema));
        statements.addAll(cleanTables(schema));

        List<SqlStatement> sqlStatements = new ArrayList<SqlStatement>();
        int lineNumber = 1;
        for (String statement : statements) {
            sqlStatements.add(new SqlStatement(lineNumber, statement));
            lineNumber++;
        }
        return new SqlScript(sqlStatements);
    }

    /**
     * Cleans the foreign keys in this schema.
     *
     * @param schema The schema to generate the statements for.
     * @return The drop statements.
     * @throws java.sql.SQLException when the clean statements could not be generated.
     */
    private List<String> cleanForeignKeys(String schema) throws SQLException {
        List<Map<String, String>> constrains = jdbcTemplate.queryForList("select constrid, tableid from sysconstraints");
        List<String> statements = new ArrayList<String>();
        for (Map<String, String> constrain : constrains) {
            String constrainName = getSysObjectNameById(constrain.get("constrid"));
            String tableName = getSysObjectNameById(constrain.get("tableid"));
            statements.add(String.format("alter table [%s].[%s] drop constraint [%s]",
                    schema, tableName, constrainName));
        }
        return statements;
    }

    private String getSysObjectNameById(String id) throws SQLException {
        return jdbcTemplate.queryForString("select name from sysobjects where id=" + id);
    }

    /**
     * Cleans the views in this schema.
     *
     * @param schema The schema to generate the statements for.
     * @return The drop statements.
     * @throws java.sql.SQLException when the clean statements could not be generated.
     */
    private List<String> cleanViews(String schema) throws SQLException {
        List<String> viewNames = jdbcTemplate.queryForStringList(
                "select name from sysobjects where type='V' and name not like 'sys%'");
        List<String> statements = new ArrayList<String>();
        for (String viewName : viewNames) {
            statements.add(String.format("drop view [%s].[%s]", schema, viewName));
        }
        return statements;
    }

    /**
     * Cleans the tables in this schema.
     *
     * @param schema The schema to generate the statements for.
     * @return The drop statements.
     * @throws java.sql.SQLException when the clean statements could not be generated.
     */
    private List<String> cleanTables(String schema) throws SQLException {
        List<String> tableNames = jdbcTemplate.queryForStringList(
                "select name from sysobjects where type in ('U')");
        List<String> statements = new ArrayList<String>();
        for (String tableName : tableNames) {
            statements.add(String.format("drop table [%s].[%s]", schema, tableName));
        }
        return statements;
    }
}
